﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using CodeGenerator.interfaces;

namespace CodeGenerator.Implementations
{
    class SQLiteDatabaseProvider : IDatabaseProvider
    {
        private SQLiteConnection _connection = new SQLiteConnection();
        private SQLiteCommand _command = new SQLiteCommand();
        private SQLiteDataAdapter _dataAdapter;
        private string _dbFileName = "";
        private IGeneratorModel _model;
        
        public SQLiteDatabaseProvider(IGeneratorModel Model, string dbFileName)
        {
            _model = Model;
            _dbFileName = dbFileName;
            
            OpenConnection(_dbFileName);
        }

        private void OpenConnection(string dbFileName)
        {
            if (!File.Exists(dbFileName))
                throw new FileNotFoundException("The specified database file " + Environment.NewLine + Environment.NewLine + dbFileName + Environment.NewLine + Environment.NewLine + "does not exists");
            _connection.ConnectionString = "Data Source=" + dbFileName;
            _connection.Open();
            _command = _connection.CreateCommand();
            _command.CommandType = CommandType.Text;
            _dataAdapter = new SQLiteDataAdapter(_command);
        }

        //TODO: reemplazar por metodos especificos

        public DatabaseEngines DatabaseEngine
        {
            get { return DatabaseEngines.SqlLite; }
        }

        public IList<IGeneratorClass> GetTables()
        {
            IList<IGeneratorClass> res = new List<IGeneratorClass>();
            DataTable dt = new DataTable();
            _command.CommandText = @"SELECT * FROM sqlite_master WHERE type='table' ORDER BY name";
            _dataAdapter.Fill(dt);
            try
            {

                return (from DataRow dr in dt.Rows
                        select new DataBaseTable(_model, dr["name"].ToString(), this)
                            into dataBaseTable
                            select new GeneratorClass(Common.Formatting.ToString_OneTwoThree(dataBaseTable.TableName), dataBaseTable)).Cast<IGeneratorClass>().ToList();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + Environment.NewLine + e.StackTrace);
                return null;
            }
            finally
            {
                string a = "";
            }
            #region "Old code without linq"
            //foreach (DataRow dr in dt.Rows)
            //{
            //    DataBaseTable dataBaseTable = new DataBaseTable(_model,dr["name"].ToString(), this);
            //    GeneratorClass newClass = new GeneratorClass("", dataBaseTable);
            //    res.Add(newClass);
            //}

            return res;
#endregion

        }

        private Type GetTableColumnType(string TableName, string ColumnName)
        {
            if (ColumnName == "")
                return typeof(String);
            return (
                       from IDatabaseColumn n in GetTableColumns(TableName)
                       where n.ColumnName.ToUpper() == ColumnName.ToUpper()
                       select n.DataType
                   ).Single();
        }
        
        public IList<IDatabaseColumn> GetTableColumns(string TableName)
        {

            //IList<IGeneratorClass> res = new List<IGeneratorClass>();
            DataTable dt = Get1RowFromTable(TableName);

            IList<IDatabaseColumn> result = new List<IDatabaseColumn>();
            foreach (DataColumn dc in dt.Columns)
            {
                DatabaseColumn newCol ;
                if(dc.DataType == typeof(System.Object))
                {
                    MessageBox.Show(
                        "Unrecognized type found on column " + Environment.NewLine + Environment.NewLine + TableName +
                        "." + dc.ColumnName + Environment.NewLine + Environment.NewLine +
                        "The column will be mapped to type Object", "Warning", MessageBoxButtons.OK,
                        MessageBoxIcon.Warning);
                } 
                
                if(dc.DataType.Name == "Byte[]")
                    newCol = new DatabaseColumn(dc.ColumnName, typeof(Byte)); //Patch for Bytes, that normally are recognized as Byte[] 
                else
                    newCol = new DatabaseColumn(dc.ColumnName, dc.DataType);
                
                result.Add(newCol);
            }
            return result; 
        }

        private DataTable Get1RowFromTable(string tableName)
        {
             DataTable dt = new DataTable();
            _command.CommandText = @"SELECT * FROM "+ tableName + " limit 1";
            _dataAdapter.Fill(dt);
            return dt;
        }

        private DataTable GetSchema(string SchemaName)
        {
            return _connection.GetSchema(SchemaName);
        }

        public IList<IUniqueConstraint> GetUniqueIndexes(string tableName)
        {
            List<IUniqueConstraint> result = new List<IUniqueConstraint>();
            DataTable dtIndexes = GetSchema("Indexes");
            DataTable dtIndexColumns = _connection.GetSchema(SQLiteMetaDataCollectionNames.IndexColumns);
            //Testform t = new Testform(dtIndexes, dtIndexColumns);
            //t.ShowDialog();
            
            var xindexes = from DataRow index in dtIndexes.Rows
                           where index["table_name"].ToString().ToUpper().Equals(tableName.ToUpper()) &&
                            ((bool)index["primary_key"]).Equals(false)   &&
                            ((bool)index["unique"]).Equals(true) 
                           select index["index_name"].ToString();

            foreach (string uc in xindexes)
            {
                string uc1 = uc;
                var colsTmp =  from DataRow indexColumn in dtIndexColumns.Rows
                                                    where indexColumn["index_name"].ToString().ToUpper().Equals(uc1.ToUpper())
                                                    orderby indexColumn["ordinal_position"]
                                                    select new DatabaseColumn(
                                                                              indexColumn["column_name"].ToString(), 
                                                                              GetTableColumnType
                                                                                (
                                                                                tableName,
                                                                                indexColumn["column_name"].ToString()
                                                                                )
                                                                             )
                                                                        ;
                List<IDatabaseColumn> cols = new List<IDatabaseColumn>(colsTmp.Count());
                
                foreach (DatabaseColumn databaseColumn in colsTmp)
                {
                    cols.Add(databaseColumn);
                }
                result.Add(new UniqueConstraint(uc, cols));
            }

            return result;

        }


        public IList<IDatabaseColumn> GetTablePrimaryKeyColumns(string tableName)
        {

            List<IDatabaseColumn> result = new List<IDatabaseColumn>();
            DataTable dtIndexes = GetSchema("Indexes");
            DataTable dtIndexColumns = _connection.GetSchema(SQLiteMetaDataCollectionNames.IndexColumns);
            //Testform t = new Testform(dtIndexes, dtIndexColumns);
            //t.ShowDialog();

            var pkIndex =  from DataRow index in dtIndexes.Rows
                             where index["table_name"].ToString().ToUpper().Equals(tableName.ToUpper()) &&
                                   ((bool)index["primary_key"]).Equals(true)
                             select index["index_name"].ToString() ;

            var colsTmp = from DataRow indexColumn in dtIndexColumns.Rows
                          where pkIndex.Count() > 0 && indexColumn["index_name"].ToString().ToUpper().Equals(pkIndex.ToList()[0].ToString().ToUpper())
                          orderby indexColumn["ordinal_position"]
                          select new DatabaseColumn(
                                                    indexColumn["column_name"].ToString(),
                                                    GetTableColumnType
                                                      (
                                                      tableName,
                                                      indexColumn["column_name"].ToString()
                                                      )
                                                   )
                      ;
         

            foreach (DatabaseColumn databaseColumn in colsTmp)
            {
                result.Add(databaseColumn);
            }
            return result;
        } 
        //public IList<IDatabaseColumn> GetTablePrimaryKeyColumns(string tableName)
        //{
        //    DataTable dtIndexes = GetSchema("Indexes");
        //    DataTable dtIndexColumns = _connection.GetSchema("IndexColumns");

        //    //Testform t = new Testform(dtIndexes, dtIndexColumns);
        //    //t.ShowDialog();
        //    IList<IDatabaseColumn> result = (from DataRow index in dtIndexes.Rows
        //                                     where index["table_name"].ToString().ToUpper().Equals(tableName.ToUpper()) &&
        //                                     ((bool)dtIndexes.Rows[0]["primary_key"]).Equals(true)
        //                                     from DataRow indexColumn in dtIndexColumns.Rows
        //                                     where index["index_name"].ToString().ToUpper().Equals(indexColumn["index_name"].ToString().ToUpper())
        //                                     select new DatabaseColumn(indexColumn["column_name"].ToString(), GetTypeOfColumn(tableName, indexColumn["column_name"].ToString()))).Cast<IDatabaseColumn>().ToList();

        //    //IList<IDatabaseColumn> result = (from DataRow index in dtIndexes.Rows
        //    //                                 where index["table_name"].ToString().ToUpper() == tableName.ToUpper() &&
        //    //                                 ((bool)dtIndexes.Rows[0]["primary_key"]) == true
        //    //                                 from DataRow indexColumn in dtIndexColumns.Rows
        //    //                                 where index["index_name"].ToString().ToUpper() == indexColumn["index_name"].ToString().ToUpper()
        //    //                                 select new DatabaseColumn(indexColumn["column_name"].ToString(), GetTypeOfColumn(tableName, indexColumn["column_name"].ToString()))).Cast<IDatabaseColumn>().ToList();
   

        //    return result;

        //}

        private Type GetTypeOfColumn(string TableName, string ColumnName)
        {
            return (from n in GetTableColumns(TableName)
                      where n.ColumnName == ColumnName 
                      select n.DataType).Single()
            ;
        }
        
        public IList<IForeignKey> GetTableForeignKeys(string tableName,IGeneratorModel Model )
        {

            DataTable dtForeignKeys = GetSchema("ForeignKeys");

            //Testform t = new Testform(dtForeignKeys, GetSchema("Indexes"));
            //t.ShowDialog();
            var tmpList = from DataRow fkRow in dtForeignKeys.Rows
                          where fkRow["table_name"].ToString().ToUpper() == tableName.ToUpper() &&
                                fkRow["constraint_type"].ToString().ToUpper() == "FOREIGN KEY" &&
                                fkRow["FKEY_TO_COLUMN"] != "" //Patch, sometimes there is no referenced column name
                          orderby fkRow["constraint_name"] , fkRow["fkey_from_ordinal_position"]
                          select new
                                     {
                                         ConstraintName = fkRow["constraint_name"].ToString(),
                                         TableName = fkRow["table_name"].ToString(),
                                         LocalColumnOrdinalPosition = fkRow["fkey_from_ordinal_position"].ToString(),
                                         ForeignTableName = fkRow["fkey_to_table"].ToString(),
                                         ForeignColumn = new DatabaseColumn(
                              fkRow["fkey_to_column"].ToString(),
                              GetTableColumnType(fkRow["fkey_to_table"].ToString(),
                                                 fkRow["fkey_to_column"].ToString())
                              ),
                                         LocalColumn = new DatabaseColumn(
                              fkRow["fkey_from_column"].ToString(),
                              GetTableColumnType(tableName, fkRow["fkey_from_column"].ToString())
                              ),
                              OrdinalPosition = fkRow["fkey_from_ordinal_position"]
                          };

            ForeignKey workingFk = null;
            List<IForeignKey> result = new List<IForeignKey>();
            
            foreach (var item in tmpList)
            {
                //check if the collection already have the foreing key
                //MessageBox.Show(result.Select(G => G.ForeignKeyName.ToUpper() == item.ConstraintName.ToUpper()).Count().ToString());
                var matches = from G in result
                              where G.ForeignKeyName.ToUpper() == item.ConstraintName.ToUpper()
                                  select G
                                  ;

                if (matches.Count() == 0)
                {
                    //item is not not present 

                    workingFk = new ForeignKey(Model, item.TableName, item.ConstraintName, null, item.ForeignTableName, item.ConstraintName);
                    result.Add(workingFk);
                }
                else
                {
                    var item1 = item;
                    workingFk =  (ForeignKey)result.Single(g => g.ForeignKeyName.ToUpper() == item1.ConstraintName.ToUpper());
                }
                //add the colum to the list of columns

                DataBaseColumnPair cp = new DataBaseColumnPair(item.LocalColumn,item.ForeignColumn);
                workingFk.Columns.Add(cp);
            }

            return result;  
        }

    

        //private DatabaseTypes DataColumnTypeToDotNetType(Type dataType)
        //{

        //    switch (dataType.Name.ToUpper())
        //    {
        //        case "DBTYPE.INT64":
        //            return DatabaseTypes.Counter;

        //    }
        //    return DatabaseTypes.Yesno;

        //}
    }
}
/*
COUNTER DbType.Int64 
AUTOINCREMENT DbType.Int64 
IDENTITY DbType.Int64 
LONG DbType.Int64 
TINYINT DbType.Byte 
INTEGER DbType.Int64 
INT DbType.Int32 
VARCHAR DbType.String 
NVARCHAR DbType.String 
CHAR DbType.String 
NCHAR DbType.String 
TEXT DbType.String 
NTEXT DbType.String 
STRING DbType.String 
DOUBLE DbType.Double 
FLOAT DbType.Double 
REAL DbType.Single 
BIT DbType.Boolean 
YESNO DbType.Boolean 
LOGICAL DbType.Boolean 
BOOL DbType.Boolean 
NUMERIC DbType.Decimal 
DECIMAL DbType.Decimal 
MONEY DbType.Decimal 
CURRENCY DbType.Decimal 
TIME DbType.DateTime 
DATE DbType.DateTime 
TIMESTAMP DbType.DateTime 
DATETIME DbType.DateTime 
BLOB DbType.Binary 
BINARY DbType.Binary 
VARBINARY DbType.Binary 
IMAGE DbType.Binary 
GENERAL DbType.Binary 
OLEOBJECT DbType.Binary 
GUID DbType.Guid 
UNIQUEIDENTIFIER DbType.Guid 
MEMO DbType.String 
NOTE DbType.String 
LONGTEXT DbType.String 
LONGCHAR DbType.String 
SMALLINT DbType.Int16 
BIGINT DbType.Int64 
LONGVARCHAR DbType.String 
SMALLDATE DbType.DateTime 
SMALLDATETIME DbType.DateTime 
*/


